const Koa = require('koa')
const cors = require('koa-cors')
const bodypaser = require('koa-bodyparser')
const router = require('koa-router')()
const { Sequelize, DataTypes,Op } = require('sequelize')

const app = new Koa()

const sequelize = new Sequelize('demo01', 'sa', '123456', {
    host: 'localhost',
    dialect: 'mssql'
})

sequelize.authenticate().then(() => {
    try {
        console.log('成功');
    } catch (error) {
        console.log('失败');
    }
})
const StudentClass = sequelize.define("StudentClass", {
    name: {
        type: DataTypes.STRING,
        allowNull: false
    }
})

const Students = sequelize.define('Students', {
    name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    age: {
        type: DataTypes.INTEGER,
        allowNull: true
    },
    classId: {
        type: DataTypes.INTEGER,
        allowNull: false
    }
})

// sequelize.sync({force:true}).then(async()=>{
//     let studentClass = await StudentClass.create({
//         "name": "1班"
//     })

//     await Students.bulkCreate([
//         {
//             "name": "张三",
//             "age": 12,
//             "classId": studentClass.id
//         },
//         {
//             "name": "李四",
//             "age": 12,
//             "classId": studentClass.id
//         },
//         {
//             "name": "王五",
//             "age": 12,
//             "classId": studentClass.id
//         }
//     ])
// })

app.use(cors())
app.use(bodypaser())

router.get('/students', async (ctx, next) => {
    let keyword = ctx.request.query.keyword || ''

    let stuClass = await StudentClass.findAll()
    let arr = []
    if (keyword && keyword.length > 0) {
        let { count, rows } = await Students.findAndCountAll({
            where: {
                [Op.or]: [
                    {name:keyword},
                    {age:isNaN(keyword)?0 : parseInt(keyword)},
                    {id:isNaN(keyword)?0 : parseInt(keyword)}
                ]
            },
            order: ['id']
        })
        console.log(rows);
        rows.forEach(item => {
            let sclass = stuClass.find(obj=>{
                return obj.id === item.classId
            })
            item.dataValues.className = sclass.dataValues.name
            arr.push(item)
        })
    } else {
        let { count, rows } = await Students.findAndCountAll()
        rows.forEach(item => {
            let sclass = stuClass.find(obj=>{
                return obj.id === item.classId
            })
            item.dataValues.className = sclass.dataValues.name
            arr.push(item)
        })
    }
    ctx.body = {
        code: 1000,
        msg: "查询成功",
        data: arr
    }
}).post('/students',async(ctx)=>{
    let data = ctx.request.body
    data.classId =1
    let student =await Students.create(data)
    ctx.body = {
        code: 1000,
        msg: "添加成功",
        data: student
    }
}).put('/students/:id',async(ctx)=>{
    let id = ctx.request.params.id
    let data = ctx.request.body
    data.classId = 1

    await Students.update(data,{
        where: {
            id: id
        }
    })
    let list = await Students.findAll({
        where: {
            id:id
        }
    })
    ctx.body = {
        code: 1000,
        msg: "修改成功",
        data: list
    }
}).delete('/students/:id',async(ctx)=>{
    let id = ctx.request.params.id
    await Students.destroy({
        where: {
            id: id
        }
    })
    ctx.body = {
        code: 1000,
        msg: "删除成功",
        data: id
    }
}).get('/students/:id',async(ctx)=>{
    let id = ctx.request.params.id
    let data = await Students.findAll({
        where:{
            id:id
        }
    })
    ctx.body = {
        code: 6000,
        msg: "查找成功",
        data: data
    }
})

app.use(router.routes())

app.listen(3000, () => {
    console.log(`http://localhost:3000`);
})